﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_USR_FAVORITE_SEARCH_Get_Favorite_List')
	BEGIN
		DROP Procedure usp_UPDMS_USR_FAVORITE_SEARCH_Get_Favorite_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_USR_FAVORITE_SEARCH_Get_Favorite_List
**	Desc : 메뉴핫링크를 Select한다.
**	Test Exec Query : Exec usp_UPDMS_USR_FAVORITE_SEARCH_Get_Favorite_List 'smstyle', 'ko'
**	Called by : Com_Dac_UPDMS_USR_FAVORITE_SEARCH.cs
**	Program ID : Favorite
**	Auth : 송시명
**	Date : 2014-08-27
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_USR_FAVORITE_SEARCH_Get_Favorite_List]
@ls_user_id nvarchar(20),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT CASE rslt.Pgm_Div WHEN 'D' THEN rslt.Pgm_Nm
                         ELSE '　　[' + rslt.Pgm_Nm + '] ' + Title
       END [Text],
       CASE rslt.Pgm_Div WHEN 'D' THEN 'javascript:void(0);'
                         ELSE '../../' + rslt.Pgm_Class + '/' + rslt.Pgm_Url + '?sm=' + rslt.Dir_Depth + Search_Condition
       END [Value]
  FROM (
       SELECT ucp.Pgm_Id,
              ucp.Pgm_Class,
              CASE @ls_lang_set WHEN 'ko' THEN ucp.Pgm_Nm
                                ELSE ucp.Pgm_Nm_Eng
              END Pgm_Nm,
              ucp.Pgm_Url,
              ucp.Pgm_Div,
              ucp.Dir_Depth,
              tmp.Title,
              tmp.Search_Condition
         FROM UPDMS_COM_PGM AS ucp WITH(NOLOCK)
         JOIN (
              SELECT Pgm_Id,
                     '' AS Title,
                     '' AS Search_Condition
                FROM UPDMS_COM_PGM WITH(NOLOCK)
               WHERE Pgm_Div = 'D'
                 AND LEFT(Dir_Depth, 3) IN (
                       SELECT LEFT(B.Dir_Depth, 3)
                         FROM UPDMS_USR_FAVORITE_SEARCH A WITH(NOLOCK)
                         JOIN UPDMS_COM_PGM B WITH(NOLOCK) ON A.Pgm_Id = B.Pgm_Id
                        WHERE [User_Id] = @ls_user_id
                     )
              UNION ALL
              SELECT Pgm_Id,
                     Title,
                     Search_Condition
                FROM UPDMS_USR_FAVORITE_SEARCH WITH(NOLOCK)
               WHERE [User_Id] = @ls_user_id
              ) tmp
           ON ucp.Pgm_Id = tmp.Pgm_Id
       ) rslt
 ORDER BY rslt.Dir_Depth

GO